# _*_ coding utf-8 _*_
# george
# time: 2025/1/7上午9:34
# name: views.py
# comment: 路由+视图函数
import bdb
import datetime
import os

from flask import render_template, Blueprint, request, make_response, Response, redirect, url_for, session
from sqlalchemy import desc

from .models import *

# 创建蓝图
blue = Blueprint("user", __name__)


@blue.route("/")
def home():
    data = {
        "name": "ikun",
        "age": 20,
        "likes": ["ball", "sing", "dance"]

    }
    return render_template("home.html", **data)


# 单表操作

# 增加数据
@blue.route("/useradd/")
def user_add():
    # 添加一条数据
    # u = User()
    # u.name = "kun"
    # u.age = 24
    # db.session.add(u)  # 将u对象添加到session中
    # db.session.commit()  # 同步到数据库中

    # 同时添加多条数据
    users = []
    for i in range(10, 30):
        u = User()
        u.name = "冰冰" + str(i)
        u.age = i
        users.append(u)
    try:
        db.session.add_all(users)
        db.session.commit()  # 事务提交
    except Exception as e:
        db.session.rollback()  # 事务回滚
        db.session.flush()
        return "fail:" + str(e)

    return "success!!"


# 删除数据
# 找到要删除的数据,然后删除
@blue.route("/userdel/")
def user_del():
    u = User.query.first()  # 查询第一条数据
    # print(u) # <User 3>
    try:
        db.session.delete(u)
        db.session.commit()
    except Exception as e:
        return "fail:" + str(e)
    return "delete success!!"


# 修改数据
# 找到要修改的数据,然后修改数据
@blue.route("/usermd/")
def user_md():
    u = User.query.first()
    try:
        u.age = 1000
        db.session.commit()
    except Exception as e:
        return "fail" + str(e)
    return f"修改{u.name} success!!"


# 查询数据
# print(users)  # [<User 4>, <User 5>, <User 6>, <User 7>]
# print(User.query, type(User.query))  # SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age FROM user ,<class 'flask_sqlalchemy.query.Query'>

# all(): 返回所有数据,返回列表 : [<User 4>, <User 5>, <User 6>, <User 7>]
# filter() 过滤,类似于SQL中的where,得到查询集
# get(),查询到对应主键的数据对象
@blue.route("/userselect/")
def user_select():
    # all(): 返回所有数据,返回列表 : [<User 4>, <User 5>, <User 6>, <User 7>]
    users = User.query.all()
    # print(users)  # [<User 4>, <User 5>, <User 6>, <User 7>]
    # print(User.query, type(
    #     User.query))  # SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age FROM user ,<class 'flask_sqlalchemy.query.Query'>

    # filter 过滤,类似于SQL中的where
    # users = User.query.filter()
    # print(users, type(users))  # <class 'flask_sqlalchemy.query.Query'>
    # print(list(users))
    # print(os.environ)

    # get(),查询到对应主键的数据对象
    user = User.query.get(5)
    # print(user, type(user))  # <User 5> <class 'App.models.User'>
    # print(user.name, user.age)  # 获取数据的属性

    # filter => where
    # filter_by() : 用于等值操作
    # user = User.query.filter(User.id == 5)
    # print(user, type(user))  # SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age FROM user WHERE user.id = %(id_1)s <class 'flask_sqlalchemy.query.Query'>
    # print(list(user))  # print(list(user))

    # user = User.query.filter_by(id=5)
    # print(user,type(user))  # 和上面的一样,只是这个只能用于等值操作

    # first()第一条数据
    # first_or_404():第一条数据不存在就抛出404
    # user = User.query.filter_by(age=100).first_or_404()
    # print(user)

    #count() 统计数据的数量
    # users = User.query.count()
    # print(users)  # 4

    # limit():前几条数据
    # offset 跳过前几条
    users = User.query.offset(3).limit(4) # 跳过前三条取第4条

    # order_by():排序
    users = User.query.order_by(desc(User.id))
    print(list(users))

    return f"{users}"
